﻿using Common;
using DAL.DBUtility;
using Enums;
using Enums.Sys;
using Model.Organization;
using Model.Sys;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL.Organization
{
    public class EnterpriseDAL
    {
        /// <summary>
        /// 获取所有的数据信息
        /// 根据条件
        /// </summary>
        /// <returns></returns>
        public List<EnterpriseModel> GetList(string strWhere, string term)
        {
            String sql = @"  select oe.ID, oe.EnterpriseName,oe.PersonCharge,oe.PersonMobile,oes.EnterpriseName as SuperiorsOrganizationName  ,oe.Code ,oe.CreateLoginName from Organization_Enterprise oe left join Organization_Enterprise oes
                          on oe.SuperiorsOrganization=oes.ID  WHERE 1=1  AND  oe.ViewDel=@ViewDel {0}   and (oe.EnterpriseName like '%" + @term + "%' or oe.Code like '%" + @term + "%')";
            sql = string.Format(sql, strWhere);
            SqlParameter[] parmer =  {
                new SqlParameter("@ViewDel",EnumManagement.GetEnumValue(SysEnum.NoDele)),
                 new SqlParameter("@term",term)
           };
            DataTable dt = DbHelperSQL.Query(sql, parmer).Tables[0];
            List<EnterpriseModel> modelList = ListDataSet.DataSetToIList<EnterpriseModel>(dt).ToList();
            return modelList;
        }
        /// <summary>
        /// 根据主键编号查询对象
        /// </summary>
        /// <returns></returns>
        public EnterpriseModel GetModel(int id)
        {
            EnterpriseModel model = new EnterpriseModel();
            string sql = @"select * from Organization_Enterprise where ID=@ID ";
            SqlParameter[] parmars ={
                    new SqlParameter("@ID",PageValidate.ConvertToTrimInt(id))
               };
            SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parmars);
            if (sdr.Read())
            {
                model = CommonModel(sdr);
            }
            sdr.Close();
            return model;
        }

        private EnterpriseModel CommonModel(SqlDataReader sdr)
        {
            EnterpriseModel model = new EnterpriseModel();
            model.ID = PageValidate.ConvertToTrimInt(sdr["ID"]);

            model.Code = PageValidate.ConvertToTrimString(sdr["Code"]);
            model.ViewFlag = PageValidate.ConvertToTrimInt(sdr["ViewFlag"]);
            model.EnterpriseName = PageValidate.ConvertToTrimString(sdr["EnterpriseName"]);
            model.SuperiorsOrganization = PageValidate.ConvertToTrimInt(sdr["SuperiorsOrganization"]);
            model.DisabledTime = PageValidate.ConvertToDateTime(sdr["DisabledTime"]);//激活方式
            model.ViewDel = PageValidate.ConvertToTrimInt(sdr["ViewDel"]);
            model.CreateLoginID = PageValidate.ConvertToTrimInt(sdr["CreateLoginID"]);
            model.CreateLoginName = PageValidate.ConvertToTrimString(sdr["CreateLoginName"]);
            model.CreateDate = PageValidate.ConvertToDateTime(sdr["CreateDate"]);
            model.ChangeLoginID = PageValidate.ConvertToTrimInt(sdr["ChangeLoginID"]);
            model.ChangeLoginName = PageValidate.ConvertToTrimString(sdr["ChangeLoginName"]);
            model.ChangeDate = PageValidate.ConvertToDateTime(sdr["ChangeDate"]);
            model.PersonCharge = PageValidate.ConvertToTrimString(sdr["PersonCharge"]);
            model.PersonMobile = PageValidate.ConvertToTrimString(sdr["PersonMobile"]);
            model.LandlinePhone = PageValidate.ConvertToTrimString(sdr["LandlinePhone"]);
            return model;
        }



        /// <summary>
        /// 操作
        /// </summary>
        /// <param name="Content"></param>
        /// <returns></returns>
        public ResultEntity SaveData(EnterpriseModel model, SysUserModel userModel)
        {
            ResultEntity entity = new ResultEntity();
            if (model.ID == 0)
                entity = Insert(model, userModel);
            else
                entity = Edit(model, userModel);
            
            return entity;
        }
        /// <summary>
        /// 保存方法
        /// </summary>
        /// <param name="model"></param>
        /// <param name="userModel"></param>
        /// <returns></returns>
        public ResultEntity Insert(EnterpriseModel model, SysUserModel userModel)
        {
            string sql = @"INSERT INTO Organization_Enterprise (ViewDel,ViewFlag,EnterpriseName,SuperiorsOrganization,
              PersonCharge,PersonMobile,CreateDate,CreateLoginID,CreateLoginName,Code,ChangeLoginID,ChangeLoginName,ChangeDate,LandlinePhone) 
              VALUES(@ViewDel,@ViewFlag,@EnterpriseName,@SuperiorsOrganization,
              @PersonCharge,@PersonMobile,@CreateDate,@CreateLoginID,@CreateLoginName,@Code,@ChangeLoginID,@ChangeLoginName,@ChangeDate,@LandlinePhone);select @@IDENTITY;";
            SqlParameter[] parmas ={
                                  new SqlParameter("@ViewDel",EnumManagement.GetEnumValue(SysEnum.NoDele)),
                                  new SqlParameter("@ViewFlag",EnumManagement.GetEnumValue(SysEnum.Enable)),
                                  new SqlParameter("@EnterpriseName",model.EnterpriseName),
                                  new SqlParameter("@SuperiorsOrganization",model.SuperiorsOrganization),
                                  new SqlParameter("@PersonCharge",model.PersonCharge),
                                  new SqlParameter("@PersonMobile",model.PersonMobile),
                                  new SqlParameter("@CreateDate",DateTime.Now),
                                   
                                  new SqlParameter("@CreateLoginID",userModel.ID),
                                  new SqlParameter("@CreateLoginName",userModel.LoginName),
                                  new SqlParameter("@Code",model.Code),
                                  new SqlParameter("@ChangeLoginID",userModel.ID),
                                  new SqlParameter("@ChangeLoginName",userModel.LoginName),
                                  new SqlParameter("@ChangeDate",DateTime.Now),
                                   new SqlParameter("@LandlinePhone",model.LandlinePhone)
                                  };

            return DbHelperSQL.CommonResult(DbHelperSQL.GetSingle(sql, parmas));
        }
        /// <summary>
        /// 编辑方法
        /// </summary>
        /// <returns></returns>
        public ResultEntity Edit(EnterpriseModel model, SysUserModel userModel)
        {
            string sql = @"update Organization_Enterprise set EnterpriseName=@EnterpriseName,SuperiorsOrganization=@SuperiorsOrganization,
              PersonCharge=@PersonCharge,PersonMobile=@PersonMobile,Code=@Code,ChangeLoginID=@ChangeLoginID,ChangeLoginName=@ChangeLoginName,ChangeDate=@ChangeDate , LandlinePhone=@LandlinePhone where ID=@ID";
            SqlParameter[] parameters ={
                                      new SqlParameter("@EnterpriseName",model.EnterpriseName),
                                      new SqlParameter("@SuperiorsOrganization",model.SuperiorsOrganization),
                                     new SqlParameter("@PersonCharge",model.PersonCharge),
                                      new SqlParameter("@PersonMobile",model.PersonMobile),
                                      new SqlParameter("@Code",model.Code),
                                     new SqlParameter("@ChangeLoginID",userModel.ID),
                                      new SqlParameter("@ChangeLoginName",userModel.LoginName),
                                      new SqlParameter("@ChangeDate",DateTime.Now),
                                        new SqlParameter("@LandlinePhone",model.LandlinePhone),
                                     new SqlParameter("@ID",model.ID)
                                     };
            return DbHelperSQL.CommonResult(DbHelperSQL.ExecuteSql(sql, parameters));
        }

        public string GetEnterpriseCode()
        {
            string sql = @"select REPLICATE('0',4-len(MAX(CONVERT(int, Code))))+cast(MAX(CONVERT(int,Code)+1) as nvarchar) from Organization_Enterprise";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            return dt.Rows[0][0].ToString();
        }
        public EnterpriseModel GetModelByCode(int Code)
        {
            EnterpriseModel model = new EnterpriseModel();
            string sql = @"  select * from Organization_Enterprise where Code=@Code ";
            SqlParameter[] parmars ={
                    new SqlParameter("@Code",Code)
               };
            SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parmars);
            if (sdr.Read())
            {
                model = CommonModel(sdr);
            }
            sdr.Close();
            return model;
        }
        public List<EnterpriseModel> GetSonEnterprise(int enterpriseId)
        {

            string sql = string.Format(@"
with tempa(ID, ViewDel, ViewFlag, Code, EnterpriseName, 
 SuperiorsOrganization, PersonCharge, PersonMobile,
  LandlinePhone, CreateLoginID, CreateLoginName, CreateDate, 
  ChangeLoginID, 
  ChangeLoginName, ChangeDate) as 
(
SELECT ID, ViewDel, ViewFlag, Code, EnterpriseName, SuperiorsOrganization, 
	PersonCharge, PersonMobile, LandlinePhone, 
	CreateLoginID, CreateLoginName, CreateDate, ChangeLoginID, 
	ChangeLoginName, ChangeDate
	FROM Organization_Enterprise oe
	where ID={0}
	union all
	SELECT oe.ID, oe.ViewDel, oe.ViewFlag, oe.Code, oe.EnterpriseName, 
	 oe.SuperiorsOrganization, 
	oe.PersonCharge, oe.PersonMobile, 
	oe.LandlinePhone,
	 oe.CreateLoginID, oe.CreateLoginName, oe.CreateDate, oe.ChangeLoginID, oe.ChangeLoginName, oe.ChangeDate
	 
	FROM Organization_Enterprise oe
	 inner join tempa on tempa.ID=oe.SuperiorsOrganization
	)
	select * from tempa where  ViewDel={1} and ViewFlag={2}", enterpriseId, EnumManagement.GetEnumValue(SysEnum.NoDele), EnumManagement.GetEnumValue(SysEnum.Enable));

            DataTable tbl = new DataTable();
            tbl = DBUtility.DbHelperSQL.GetTable(sql);
            return ListDataSet.DataSetToIList<EnterpriseModel>(tbl).ToList();

        }
        public List<EnterpriseModel> GetALLButNoSelf(int enterpriseId)
        {

            string sql = string.Format(@"
with tempa(ID, ViewDel, ViewFlag, Code, EnterpriseName,
 SuperiorsOrganization, PersonCharge, PersonMobile,
  LandlinePhone, CreateLoginID, CreateLoginName, CreateDate, ChangeLoginID, 
  ChangeLoginName, ChangeDate) as 
(
SELECT ID, ViewDel, ViewFlag, Code, EnterpriseName,SuperiorsOrganization,  PersonCharge, PersonMobile, 
	LandlinePhone,  CreateLoginID, CreateLoginName, CreateDate, ChangeLoginID, ChangeLoginName, ChangeDate
	FROM Organization_Enterprise oe
	where ID={0}
	union all
	SELECT oe.ID, oe.ViewDel, oe.ViewFlag, oe.Code, oe.EnterpriseName, oe.SuperiorsOrganization, 
	oe.PersonCharge, oe.PersonMobile, 
	oe.LandlinePhone,
	 oe.CreateLoginID, oe.CreateLoginName, oe.CreateDate, oe.ChangeLoginID, oe.ChangeLoginName, oe.ChangeDate
	FROM Organization_Enterprise oe
	 inner join tempa on tempa.ID=oe.SuperiorsOrganization
	)
	select * from tempa where  ViewDel={1} and ViewFlag={2} and ID !={3}", enterpriseId, EnumManagement.GetEnumValue(SysEnum.NoDele), EnumManagement.GetEnumValue(SysEnum.Enable), enterpriseId);

            DataTable tbl = new DataTable();
            tbl = DBUtility.DbHelperSQL.GetTable(sql);
            return ListDataSet.DataSetToIList<EnterpriseModel>(tbl).ToList();

        }
    }
}
